<?php
/**
 *
 * @category    API
 * @package     Api_Object
 * @subpackage  Model
 * @author      trungpm
 */
class Api_Model_Object {
    const table_name = 'tblvgt_object';
    const table_contract = 'tblvgt_contract';

    /* Check that the name exists and is 300 characters or less long */
    function VerifyObjectName($ObjectName, $i, $Errors) {
        if ((strlen ( $ObjectName ) < 1) or (strlen ( $ObjectName ) > 300)) {
            $Errors [$i] = IncorrectObjectNameLength;
        }
        return $Errors;
    }

    /*
     * Verify that the object nam is valid, and doesn't already exist. author: trungpm
     */
    function VerifyObjectNameExists($ObjectName, $i, $Errors, $db) {
        $Searchsql = 'SELECT count(object_id) as c FROM tblvgt_object WHERE object_name=N\'' . $ObjectName . '\'';
        $SearchResult = DB_query ( $Searchsql, $db );
        $answer = DB_fetch_array ( $SearchResult );
        if ($answer ['c'] != 0) {
            $Errors [$i] = ObjectNameAlreadyExists;
        }
        DB_free_result ( $SearchResult );
        return $Errors;
    }

    /*
     * Verify that the object nam is valid, and doesn't already exist. author: trungpm
     */
    function VerifyObjectNumberExists($ObjectNumber, $i, $Errors, $db) {
        $Searchsql = 'SELECT count(object_id) as c FROM tblvgt_object WHERE locked = 0 AND object_number=N\'' . $ObjectNumber . '\'';
        $SearchResult = DB_query ( $Searchsql, $db );
        $answer = DB_fetch_array ( $SearchResult );
        if ($answer ['c'] != 0) {
            $Errors [$i] = ObjectNameAlreadyExists;
        }
        DB_free_result ( $SearchResult );
        return $Errors;
    }

    /*
     * Get user by Object Name. author: trungpm
     */
    function GetObjectByName($ObjectName, $user, $password) {
        $db = db ( $user, $password );
        if (gettype ( $db ) == 'integer') {
            $Errors [0] = NoAuthorisation;
            return $Errors;
        }
        $ObjectName = DB_escape_string ( $ObjectName );
        $Errors = $this->VerifyObjectNameExists ( $ObjectName, sizeof ( $Errors ), $Errors, $db );
        if (sizeof ( $Errors ) == 0) {
            return $Errors;
        }
        $sql = 'SELECT object_id,
                    object_number,
                    object_name,
                    object_company,
                    object_address,
                    object_type
                FROM tblvgt_object WHERE object_name=N\'' . $ObjectName . '\'';
        $result = DB_Query ( $sql, $db );
        if (sizeof ( $Errors ) != 0) {
            $arrResult = DB_fetch_array ( $result );
            DB_free_result ( $result );
            return $arrResult;
        } else {
            return $Errors;
        }
    }

    /*
     * Get user by Object Name. author: trungpm
     */
    function GetObjectByNumber($ObjectNumber, $user, $password) {
        $db = db ( $user, $password );
        if (gettype ( $db ) == 'integer') {
            $Errors [0] = NoAuthorisation;
            return $Errors;
        }
        $ObjectNumber = DB_escape_string ( $ObjectNumber );
        $Errors = $this->VerifyObjectNumberExists ( $ObjectNumber, sizeof ( $Errors ), $Errors, $db );
        if (sizeof ( $Errors ) == 0) {
            return $Errors;
        }
        $sql = 'SELECT object.object_id,
                    object.object_number,
                    object.object_name,
                    object.object_company,
                    object.object_address,
                    object.object_type,
                    contract.contract_id,
                    contract.contract_number
                FROM tblvgt_object as object
                LEFT JOIN tblvgt_contract as contract
                ON object.object_id = contract.object_id
                WHERE locked = 0 AND object_number=N\'' . $ObjectNumber . '\'';
        $result = DB_Query ( $sql, $db );
        if (sizeof ( $Errors ) != 0) {
            $arrResult = DB_fetch_array ( $result );
            DB_free_result ( $result );
            return $arrResult;
        } else {
            return $Errors;
        }
    }

    /*
     * Get user by Object Name. author: trungpm
     */
    function GetObjectById($ObjectId, $user, $password) {
        $db = db ( $user, $password );
        if (gettype ( $db ) == 'integer') {
            $Errors [0] = NoAuthorisation;
            return $Errors;
        }
        $sql = 'SELECT object.object_id,
                    object.object_number,
                    object.object_name,
                    object.object_company,
                    object.object_address,
                    object.object_type,
                    contract.contract_number
                FROM tblvgt_object as object
                LEFT JOIN tblvgt_contract as contract
                ON object.object_id = contract.object_id
                WHERE object.locked = 0 AND object.object_id=N\'' . $ObjectId . '\'';
        $result = DB_Query ( $sql, $db );
        $arrResult = DB_fetch_array ( $result );
        DB_free_result ( $result );
        return $arrResult;
    }

    /* Insert a new object */
    function InsertObject($ObjectDetails, $user, $password) {
        $Errors = array ();
        $db = db ( $user, $password );
        if (gettype ( $db ) == 'integer') {
            $Errors [0] = NoAuthorisation;
            return $Errors;
        }
        foreach ( $ObjectDetails as $key => $value ) {
            $ObjectDetails [$key] = DB_escape_string ( $value );
        }
        $Errors = $this->VerifyObjectName ( $ObjectDetails ['object_name'], sizeof ( $Errors ), $Errors );
        $FieldNames = '';
        $FieldValues = '';
        foreach ( $ObjectDetails as $key => $value ) {
            $FieldNames .= $key . ', ';
            $FieldValues .= 'N\'' . $value . '\', ';
        }
        $sql = 'INSERT INTO tblvgt_object (' . substr ( $FieldNames, 0, - 2 ) . ') ' . 'VALUES (' . substr ( $FieldValues, 0, - 2 ) . ') ';
        if (sizeof ( $Errors ) == 0) {
            $result = DB_Query ( $sql, $db );
            if (! $result) {
                $Errors [0] = DatabaseInsertFailed;
            }
        }
        return $Errors;
    }

    /*
     * Insert a new object and return key huudatxm
     */
    function insert($ObjectDetails, $user, $password) {
        $Errors = array ();
        $db = db ( $user, $password );
        if (gettype ( $db ) == 'integer') {
            $Errors [0] = NoAuthorisation;
            return $Errors;
        }

        $Errors = $this->VerifyObjectName ( $ObjectDetails ['object_name'], sizeof ( $Errors ), $Errors );

        $FieldNames = '';
        $FieldValues = '';
        foreach ( $ObjectDetails as $key => $value ) {
            $FieldNames .= $key . ", ";
            $FieldValues .= 'N\'' . $value . '\', ';
        }
        $FieldNames .= 'created_date, ';
        $FieldValues .= 'N\'' . date ( 'Y-m-d' ) . '\', ';
        $FieldNames .= 'created_by, ';
        $FieldValues .= 'N\'' . $_SESSION ['UserID'] . '\', ';
        $FieldNames .= 'updated_date, ';
        $FieldValues .= 'N\'' . date ( 'Y-m-d' ) . '\', ';
        $FieldNames .= 'updated_by, ';
        $FieldValues .= 'N\'' . $_SESSION ['UserID'] . '\', ';

        $sql = 'INSERT INTO tblvgt_object (' . substr ( $FieldNames, 0, - 2 ) . ') ' . ' VALUES (' . substr ( $FieldValues, 0, - 2 ) . ') ';
        if (sizeof ( $Errors ) == 0) {
            DB_Query ( $sql, $db );
            return $_SESSION ['LastInsertId'];
        }
        return 0;
    }

    /*
     * update huudatxm
     */
    /*
    function update($ObjectDetails, $user, $password) {
        $Errors = array();
        $db = db($user, $password);
        if (gettype($db) == 'integer') {
            $Errors[0] = NoAuthorisation;
            return $Errors;
        }

        $val_str = $ObjectDetails['field']. ' = \''.$ObjectDetails['value'].'\'';
        $val_str .= ', updated_date = \''.date('Y-m-d').'\' ';
        $val_str .= ', updated_by = \''.$_SESSION['UserID'].'\' ';

        $sql = 'UPDATE tblvgt_object SET '.$val_str;
        $sql .= ' WHERE object_id = '.$ObjectDetails['object_id'];

        if (sizeof($Errors) == 0) {
            return DB_Query($sql, $db);
        }
        return 0;
    }*/

    function ModifyObject($ObjectId, $ObjectDetails, $user, $password) {
        $Errors = array ();
        $db = db ( $user, $password );
        if (gettype ( $db ) == 'integer') {
            $Errors [0] = NoAuthorisation;
            return $Errors;
        }
        foreach ( $ObjectDetails as $key => $value ) {
            $ObjectDetails [$key] = DB_escape_string ( $value );
        }
        $sql = 'UPDATE tblvgt_object SET ';
        foreach ( $ObjectDetails as $key => $value ) {
            $sql .= $key . '=N\'' . str_replace ( "\'", "''", $value ) . '\', ';
        }
        $sql = substr ( $sql, 0, - 2 ) . ' WHERE object_id=' . $ObjectId;

        if (sizeof ( $Errors ) == 0) {
            $result = DB_Query ( $sql, $db );
            if (! isset ( $result ) || ! $result) {
                $Errors [0] = DatabaseUpdateFailed;
            } else {
                $Errors = array ();
            }
        }
        return $Errors;
    }

    /*
     * update
     */
    function update($object_id, $field, $value) {
        // Check user permission
        $db = db ( $_SESSION ['UserID'], $_SESSION ['Password'] );
        if (gettype ( $db ) == 'integer') {
            $Errors [0] = NoAuthorisation;
            return $Errors;
        }
        ;
        $val_str = "$field = N'$value'";
        $val_str .= ', updated_date = N\'' . date ( 'Y-m-d' ) . '\' ';
        $val_str .= ', updated_by = N\'' . $_SESSION ['UserID'] . '\' ';

        $sql = "UPDATE " . self::table_name . " SET $val_str";
        $sql .= " WHERE object_id = $object_id";

        if (sizeof ( $Errors ) == 0) {
            return DB_Query ( $sql, $db );
        }
        return 0;
    }

    /*
     * Get objects by conditions author: datnh
     */
    function loadObjects($typeArr, $limit = null, $start = null, &$total, $user, $password, $condition = array()) {
        $db = db ( $user, $password );
        if (gettype ( $db ) == 'integer') {
            $Errors [0] = NoAuthorisation;
            return $Errors;
        }

        $where = array ();
        $conds = '';
        if (! empty ( $condition )) {
            $arr = array ();
            foreach ( $condition as $key => $value ) {
                $arr [] = " $key LIKE $value ";
            }
            $conds = implode ( " AND ", $arr );
            $where [] = $conds;
        }

        if (isset ( $typeArr ) && is_array ( $typeArr ) && ! empty ( $typeArr )) {
            $where [] = "object_type IN (" . implode ( ",", $typeArr ) . ")";
        }

        if (! empty ( $where )) {
            $where = "WHERE locked = 0 AND " . implode ( " AND ", $where );
        } else {
            $where = "";
        }
        $sql = "SELECT COUNT(object_id) AS size FROM tblvgt_object $where";
        $stmt = DB_Query ( $sql, $db );
        $obj = DB_fetch_object ( $stmt );
        $total = intval ( $obj->size );

        DB_free_result ( $result );

        $sql = "SELECT @rownum:=@rownum+1 AS rownum,
                    o.object_id,
                    o.object_number ,
                    o.object_name,
                    o.object_real_name,
                    o.object_company,
                    o.object_address,
                    o.object_phone,
                    o.object_mobile,
                    o.object_email,
                    o.object_fax,
                    o.object_type,
                    c.contract_id,
                    c.contract_number,
                    DATE_FORMAT(c.contract_date,'%d/%m/%Y') AS contract_date,
                    DATE_FORMAT(c.contract_end,'%d/%m/%Y') AS contract_end
                FROM   tblvgt_object AS o
                LEFT JOIN tblvgt_contract AS c ON o.object_id = c.object_id
                JOIN (SELECT @rownum:=0) r ON 1 = 1
                $where";

        if (isset ( $start ) && isset ( $limit )) {
            $sql .= " LIMIT $start, $limit";
        }

        $result = DB_Query ( $sql, $db );

        if (DB_has_rows ( $result ) == 0) {
            return null;
        } else {
            return $result;
        }
    }

    /*
     * delete huudatxm
     */
    function delete($object_id, $user, $password) {
        $Errors = array ();
        $db = db ( $user, $password );
        if (gettype ( $db ) == 'integer') {
            $Errors [0] = NoAuthorisation;
            return $Errors;
        }
        $checkId = $this->verifyFieldExist ( 'object_id', $object_id );
        if ($checkId == 1) {
            $val_str = 'locked = 1';
            $val_str .= ', updated_date = N\'' . date ( 'Y-m-d' ) . '\' ';
            $val_str .= ', updated_by = N\'' . $_SESSION ['UserID'] . '\' ';
            $sql = "UPDATE " . self::table_name . " SET $val_str WHERE object_id = $object_id";

            if (sizeof ( $Errors ) == 0) {
                return DB_Query ( $sql, $db );
            }
        }
        return null;
    }

    /*
     * author: datnh
     */
    function GetAllObject($user, $password) {
        $db = db ( $user, $password );
        if (gettype ( $db ) == 'integer') {
            $Errors [0] = NoAuthorisation;
            return $Errors;
        }
        $SongName = DB_escape_string ( $SongName );
        $sql = 'SELECT object_id, object_number , object_name, object_type
                FROM tblvgt_object WHERE locked = 0';
        $result = DB_Query ( $sql, $db );

        if (DB_has_rows ( $result ) == 0) {
            return null;
        } else {
            return $result;
        }
        DB_free_result ( $result );
    }
    function updateContractByObject($ObjectDetails, $user, $password) {
        $Errors = array ();
        $db = db ( $user, $password );
        if (gettype ( $db ) == 'integer') {
            $Errors [0] = NoAuthorisation;
            return $Errors;
        }

        $val_str = $ObjectDetails ['field'] . ' = N\'' . $ObjectDetails ['value'] . '\'';
        $val_str .= ', updated_date = N\'' . date ( 'Y-m-d' ) . '\' ';
        $val_str .= ', updated_by = N\'' . $_SESSION ['UserID'] . '\' ';

        $sql = 'UPDATE tblvgt_contract SET ' . $val_str;
        $sql .= ' WHERE object_id = ' . $ObjectDetails ['object_id'];

        if (sizeof ( $Errors ) == 0) {
            return DB_Query ( $sql, $db );
        }
        return 0;
    }

    /*
     * author: trungpm
     */
    function GetMaxObjectNumber($type, $user, $password) {
        $Errors = array ();
        $db = db ( $user, $password );
        if (gettype ( $db ) == 'integer') {
            $Errors [0] = NoAuthorisation;
            return $Errors;
        }
        $NewObjectNumber = null;
        $sql = 'SELECT max(object_number) AS object_number FROM tblvgt_object WHERE object_type=N\'' . $type . '\'';
        $result = DB_Query ( $sql, $db );
        if (DB_has_rows ( $result ) > 0) {
            $ObjNumber = DB_fetch_object ( $result );
            $Number = $ObjNumber->object_number;
            $Number = intval ( substr ( $Number, 2, strlen ( $Number ) ) ) + 1;

            if ((strlen ( $Number ) < $_SESSION ['MaxObjectNumber']) && (strlen ( $Number ) > 0)) {
                $lengthZero = $_SESSION ['MaxObjectNumber'] - strlen ( $Number );
                $Number = str_repeat ( '0', $lengthZero ) . $Number;
            }
            // xet truong hop ca si hay nhac si
            switch ($type) {
                case 1 :
                    $NewObjectNumber = "CS" . $Number;
                    break;
                case 2 :
                    $NewObjectNumber = "NS" . $Number;
                    break;
                default :
                    return;
            }
        }

        return $NewObjectNumber;
    }

    // 2010-11-14---------------------------------------------
    function getObjectPaginates($limit, $start, &$total, $objectConditions, $contractConditions, $user, $password) {
        // Check user permission
        $db = db ( $user, $password );
        if (gettype ( $db ) == 'integer') {
            $Errors [0] = NoAuthorisation;
            return $Errors;
        }
        ;
        // Get search conditions
        $objectCondStr = null;
        if (! empty ( $objectConditions )) {
            $arr = array ();
            foreach ( $objectConditions as $cond ) {
                $arr [] = 't.' . $cond ['field'] . ' ' . $cond ['relation'] . ' ' . $cond ['value'];
            }
            $objectCondStr .= implode ( " AND ", $arr );
        }
        ;
        $contractCondStr = null;
        if (! empty ( $contractConditions )) {
            $arr = array ();
            foreach ( $contractConditions as $cond ) {
                $arr [] = 't1.' . $cond ['field'] . ' ' . $cond ['relation'] . ' ' . $cond ['value'];
            }
            $contractCondStr .= implode ( " AND ", $arr );
        }
        $where = '';
        if (! empty ( $objectCondStr )) {
            $where = 'WHERE ' . $objectCondStr;
            if (! empty ( $contractCondStr )) {
                $where .= ' AND ' . $contractCondStr;
            }
        } elseif (! empty ( $contractCondStr )) {
            $where = 'WHERE ' . $contractCondStr;
        }
        ;
        // Get query string for size
        $sql = "SELECT COUNT(*) AS size
                FROM " . self::table_name . " AS t
                LEFT JOIN " . self::table_contract . " AS t1 ON t.object_id = t1.object_id
        $where";

        // Get size
        $result = DB_Query ( $sql, $db );
        $obj = DB_fetch_object ( $result );
        $total = intval ( $obj->size );
        DB_free_result ( $result );
        // Get query string with row_num
        $query = "SELECT @rownum:=@rownum+1 AS rownum,
                    t.object_id,
                    t.object_number ,
                    t.object_name,
                    t.object_real_name,
                    t.object_company,
                    t.object_address,
                    t.object_phone,
                    t.object_mobile,
                    t.object_email,
                    t.object_fax,
                    t.object_type,
                    t1.contract_id,
                    t1.contract_number,
                    DATE_FORMAT(t1.contract_date,'%d/%m/%Y') AS contract_date,
                    DATE_FORMAT(t1.contract_end,'%d/%m/%Y') AS contract_end
                FROM " . self::table_name . " AS t
                LEFT JOIN " . self::table_contract . " AS t1 ON t.object_id = t1.object_id
                JOIN (SELECT @rownum:=0) r ON 1 = 1
                $where ORDER BY t.object_name";

        // Get query string with paginate
        if (isset ( $start ) && isset ( $limit )) {
            $query .= " LIMIT $start, $limit";
        }
        // Execute query
        $result = DB_Query ( $query, $db );
        // Return result
        if (DB_has_rows ( $result ) == 0) {
            return null;
        } else {
            return $result;
        }
    }

    /*
     * Verify field existed.
     */
    function verifyFieldExist($field, $value) {
        // Check user permission
        $db = db ( $_SESSION ['UserID'], $_SESSION ['Password'] );
        if (gettype ( $db ) == 'integer') {
            $Errors [0] = NoAuthorisation;
            return $Errors;
        }
        $Searchsql = "SELECT count(*) as c FROM " . self::table_name . " WHERE $field = N'$value'";
        $SearchResult = DB_query ( $Searchsql, $db );
        $answer = DB_fetch_array ( $SearchResult );
        if ($answer ['c'] != 0) {
            return 1;
        }
        return 0;
    }
}

?>
